﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClosedXML.Excel;
using System.IO;
using System.Globalization;
using System.Diagnostics;
using Controller.Common;
using Model;

namespace Controller.Common
{
    public class ExportExcel
    {
        private static XLWorkbook workbook;
        private static IXLWorksheet ws;

        public static void SaveExcel(string path)
        {
            try
            {
                workbook.SaveAs(path);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void InitWorkBook()
        {
            workbook = new XLWorkbook();
        }

        public static void InitWorkBook(string sheetName)
        {
            try
            {
                workbook = new XLWorkbook();
                InitNewSheet(sheetName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void InitNewSheet(string sheetName)
        {
            try
            {
                if (workbook != null)
                {
                    ws = workbook.Worksheets.Add(sheetName);
                    ws.ShowGridLines = false;
                    ws.Style.Font.FontName = "Arial";
                    ws.Style.Font.FontSize = 8;
                    ws.Style.Alignment.WrapText = true;
                    ws.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void CreateSummaryMasterChiPhi()
        {
            try
            {
                ws.Row(1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
                ws.Row(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                ws.Row(1).Style.Font.Bold = true;
                ws.Row(2).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
                ws.Row(2).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                ws.Row(2).Style.Font.Bold = true;
                ws.Column("A").Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
                ws.Column("A").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                ws.Column("A").Style.Font.Bold = true;
                ws.Column("AP").Style.Font.Bold = true;

                ws.Cell("A2").Value = "STT";
                ws.Column("A").Width = 3.57;
                ws.Cell("B2").Value = "NGÀY ORDER";
                ws.Column("B").Hide();
                ws.Cell("C2").Value = "CODE MER";
                ws.Column("C").Width = 8.43;
                ws.Cell("D2").Value = "TÊN NT";
                ws.Column("D").Width = 19.57;
                ws.Cell("E2").Value = "ĐỊA CHỈ";
                ws.Column("E").Width = 7.14;
                ws.Cell("F2").Value = "ĐƯỜNG";
                ws.Column("F").Width = 7.14;
                ws.Cell("G2").Value = "OTHER";
                ws.Column("G").Width = 7.14;
                ws.Cell("H2").Value = "PHƯỜNG";
                ws.Column("H").Width = 7.14;
                ws.Cell("I2").Value = "QUẬN";
                ws.Column("I").Width = 12;
                ws.Cell("J2").Value = "TỈNH/THÀNH";
                ws.Column("J").Width = 10.14;

                ws.SheetView.Freeze(2, 10);

                ws.Cell("K2").Value = "KHẢO SÁT";
                ws.Column("K").Width = 22;
                ws.Cell("L2").Value = "CHI PHÍ KHẢO SÁT_Đơn giá";
                ws.Column("L").Width = 10;
                ws.Cell("M2").Value = "CHI PHÍ KHẢO SÁT_Số lượng"; //13
                ws.Column("M").Width = 10;
                ws.Column("M").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("N2").Value = String.Format("CHI PHÍ KHẢO SÁT_Thành tiền{0}(1)", Environment.NewLine); //14
                ws.Column("N").Width = 10;
                ws.Column("N").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;

                ws.Cell("O2").Value = "VẬN CHUYỂN";
                ws.Column("O").Width = 29.14;
                ws.Cell("P2").Value = "CHI PHÍ VẬN CHUYỂN_Đơn giá";
                ws.Column("P").Width = 9.14;
                ws.Column("P").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("Q2").Value = "CHI PHÍ VẬN CHUYỂN_Số lượng"; //17
                ws.Column("Q").Width = 9.14;
                ws.Cell("R2").Value = String.Format("CHI PHÍ VẬN CHUYỂN_Thành tiền{0}(2)", Environment.NewLine); //18
                ws.Column("R").Width = 9.14;
                ws.Column("R").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;

                ws.Cell("S2").Value = "HẠNG MỤC THI CÔNG";
                ws.Column("S").Width = 30.29;
                ws.Cell("T2").Value = "Đơn vị tính";
                ws.Column("T").Width = 5.86;
                ws.Cell("U2").Value = "Đơn giá sản xuất"; //21
                ws.Column("U").Width = 10.86;
                ws.Column("U").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("V2").Value = "LẮP ĐẶT (NẾU CÓ)";
                ws.Column("V").Width = 17.71;
                ws.Cell("W2").Value = "Đơn vị tính";
                ws.Column("W").Width = 5.86;
                ws.Cell("X2").Value = String.Format("Đơn giá lắp đặt{0}(Nếu có)", Environment.NewLine);//24
                ws.Column("X").Width = 9.14;
                ws.Column("X").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("Y2").Value = "XIN PHÉP (NẾU CÓ)";
                ws.Column("Y").Width = 21.71;
                ws.Cell("Z2").Value = "Đơn vị tính";
                ws.Column("Z").Width = 5.86;
                ws.Cell("AA2").Value = String.Format("Đơn giá xin phép{0}(Nếu có)", Environment.NewLine); //27
                ws.Column("AA").Width = 9.14;
                ws.Column("AA").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;

                ws.Cell("AB2").Value = "Kích thước(m)_Ngang"; //28
                ws.Column("AB").Width = 8.57;
                ws.Cell("AC2").Value = "Kích thước(m)_Rộng"; //29
                ws.Column("AC").Width = 8.57;
                ws.Cell("AD2").Value = "Kích thước(m)_Số lượng"; //30
                ws.Column("AD").Width = 8.57;
                ws.Cell("AE2").Value = "Tổng diện tích (m2)"; //31
                ws.Column("AE").Width = 8.57;
                ws.Cell("AF2").Value = "Brand";
                ws.Column("AF").Width = 8.57;

                ws.Cell("AG2").Value = String.Format("Thành tiền sản xuất{0}(3)", Environment.NewLine); //33
                ws.Column("AG").Width = 11.57;
                ws.Column("AG").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("AH2").Value = String.Format("Thành tiền lắp đặt{0}(nếu có){0}(4)", Environment.NewLine); //34
                ws.Column("AH").Width = 9.14;
                ws.Column("AH").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("AI2").Value = String.Format("Thành tiền xin phép{0}(nếu có){0}(5)", Environment.NewLine); //35
                ws.Column("AI").Width = 10;
                ws.Column("AI").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;

                ws.Cell("AJ2").Value = String.Format("HẠNG MỤC KHÁC ĐƠN VỊ TÍNH NGOÀI M2{0}(NẾU CÓ)", Environment.NewLine);
                ws.Column("AJ").Width = 24.57;
                ws.Cell("AK2").Value = "Đơn vị tính";
                ws.Column("AK").Width = 5.86;
                ws.Cell("AL2").Value = String.Format("Đơn giá Hạng mục khác đơn vị tính ngoài m2{0}(Nếu có)", Environment.NewLine);
                ws.Column("AL").Width = 12.43;
                ws.Column("AL").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                ws.Cell("AM2").Value = String.Format("Số lượng Hạng mục khác đơn vị tính ngoài m2{0}(Nếu có)", Environment.NewLine); //39
                ws.Column("AM").Width = 8.57;
                ws.Cell("AN2").Value = String.Format("Thành tiền Hạng mục khác đơn vị tính ngoài m2{0}(nếu có){0}(6)", Environment.NewLine); //40
                ws.Column("AN").Width = 12.86;
                ws.Column("AN").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;

                ws.Cell("AO2").Value = "Brand";
                ws.Column("AO").Width = 12.86;
                ws.Cell("AP2").Value = String.Format("TỔNG CỘNG{0}(1)+…(6)", Environment.NewLine); //43
                ws.Column("AP").Width = 10.86;
                ws.Column("AP").Style.NumberFormat.Format = CommonConstants.EXCEL_DEFAULT_FORMAT_MONEY;
                //ws.Columns().AdjustToContents(1, 2);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void CreateDetailsTableMasterChiPhi(List<History> listHistory)
        {
            try
            {
                if (listHistory != null)
                {
                    int firstRow = 3;
                    int firstCol = 1;
                    int lastRow = firstRow + listHistory.Count;
                    int lastCol = 42;
                    int listCount = listHistory.Count;
                    int sttMergeCount = 1;

                    ws.Cell("M1").FormulaA1 = string.Format("=SUBTOTAL(9,M{0}:M{1})", firstRow, lastRow);
                    ws.Cell("N1").FormulaA1 = string.Format("=SUBTOTAL(9,N{0}:N{1})", firstRow, lastRow);
                    ws.Cell("Q1").FormulaA1 = string.Format("=SUBTOTAL(9,Q{0}:Q{1})", firstRow, lastRow);
                    ws.Cell("R1").FormulaA1 = string.Format("=SUBTOTAL(9,R{0}:R{1})", firstRow, lastRow);
                    ws.Cell("AB1").FormulaA1 = string.Format("=SUBTOTAL(9,AB{0}:AB{1})", firstRow, lastRow);
                    ws.Cell("AC1").FormulaA1 = string.Format("=SUBTOTAL(9,AC{0}:AC{1})", firstRow, lastRow);
                    ws.Cell("AD1").FormulaA1 = string.Format("=SUBTOTAL(9,AD{0}:AD{1})", firstRow, lastRow);
                    ws.Cell("AE1").FormulaA1 = string.Format("=SUBTOTAL(9,AE{0}:AE{1})", firstRow, lastRow);
                    ws.Cell("AG1").FormulaA1 = string.Format("=SUBTOTAL(9,AG{0}:AG{1})", firstRow, lastRow);
                    ws.Cell("AH1").FormulaA1 = string.Format("=SUBTOTAL(9,AH{0}:AH{1})", firstRow, lastRow);
                    ws.Cell("AI1").FormulaA1 = string.Format("=SUBTOTAL(9,AI{0}:AI{1})", firstRow, lastRow);
                    ws.Cell("AM1").FormulaA1 = string.Format("=SUBTOTAL(9,AM{0}:AM{1})", firstRow, lastRow);
                    ws.Cell("AN1").FormulaA1 = string.Format("=SUBTOTAL(9,AN{0}:AN{1})", firstRow, lastRow);
                    ws.Cell("AP1").FormulaA1 = string.Format("=SUBTOTAL(9,AP{0}:AP{1})", firstRow, lastRow);

                    for (int rowNum = 0; rowNum < listCount; rowNum++)
                    {
                        int colNum = 1;

                        if (rowNum != 0 &&
                            listHistory[rowNum].MasterChiPhi.STT.HasValue &&
                            ws.Cell(firstRow + rowNum - sttMergeCount, colNum).Value.ToString() == listHistory[rowNum].MasterChiPhi.STT.ToString())
                        {
                            var rngMerge = ws.Range(firstRow + rowNum - sttMergeCount, colNum, firstRow + rowNum, colNum);
                            rngMerge.Merge();
                            colNum++;
                            sttMergeCount++;
                        }
                        else
                        {
                            ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.STT; //A
                            sttMergeCount = 1;
                        }

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NgayOrder == null ? String.Empty : listHistory[rowNum].NgayOrder.Value.ToShortDateString(); //B
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.CodeMer; //C
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.PharmacyName; //D
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.Address; //E
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.Street; //F
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.Other == null ? String.Empty : listHistory[rowNum].NhaThuoc.Other; //G
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.Ward; //H
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.District; //I
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].NhaThuoc.Province; //J

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue.ItemDescription; //K
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue.UnitPrice.ToString(); //L
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue == null ? null : listHistory[rowNum].MasterChiPhi.SLKS; //M
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue == null ? String.Empty : (listHistory[rowNum].MasterChiPhi.Catalogue.UnitPrice * listHistory[rowNum].MasterChiPhi.SLKS).ToString(); //N

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue1 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue1.ItemDescription; //O
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue1 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue1.UnitPrice.ToString(); //P
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue1 == null ? null : listHistory[rowNum].MasterChiPhi.SLVC; //Q
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue1 == null ? String.Empty : (listHistory[rowNum].MasterChiPhi.Catalogue1.UnitPrice * listHistory[rowNum].MasterChiPhi.SLVC).ToString(); //R

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue2 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue2.ItemDescription; //S
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue2 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue2.PartNumber == null ?
                            String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue2.PartNumber; //T
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue2 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue2.UnitPrice.ToString(); //U
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue3 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue3.ItemDescription; //V
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue3 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue3.PartNumber == null ?
                            String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue3.PartNumber; //W
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue3 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue3.UnitPrice.ToString(); //X
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue4 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue4.ItemDescription; //Y
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue4 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue4.PartNumber == null ?
                            String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue4.PartNumber; //Z
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue4 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue4.UnitPrice.ToString(); //AA

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Ngang; //AB
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Rong; //AC
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.SLHMTC; //AD
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=AB{0}*AC{0}*AD{0}", firstRow + rowNum); //AE
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Brand1; //AF
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=U{0}*AE{0}", firstRow + rowNum); //AG
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=X{0}*AE{0}", firstRow + rowNum); //AH
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=AA{0}*AE{0}", firstRow + rowNum); //AI

                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue5 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue5.ItemDescription; //AJ
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue5 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue5.PartNumber == null ?
                            String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue5.PartNumber; //AK
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Catalogue5 == null ? String.Empty : listHistory[rowNum].MasterChiPhi.Catalogue5.UnitPrice.ToString(); //AL
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.SLHMK; //AM
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=AL{0}*AM{0}", firstRow + rowNum); //AN
                        ws.Cell(firstRow + rowNum, colNum++).Value = listHistory[rowNum].MasterChiPhi.Brand2; //AO
                        ws.Cell(firstRow + rowNum, colNum++).FormulaA1 = String.Format("=AN{0}+AH{0}+AI{0}+AG{0}+N{0}+R{0}", firstRow + rowNum); //AP
                    }

                    // From worksheet
                    //var rngTableDetails = ws.Range(firstRow, firstCol, lastRow, lastCol);
                    //var rngHeadersDetails = rngTableDetails.Range(1, firstCol, 1, lastCol); // The address is relative to rngTable (NOT the worksheet)
                    //rngHeadersDetails.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                    //rngHeadersDetails.Style.Font.FontColor = XLColor.White;
                    var rngDataDetails = ws.Range(1, firstCol, lastRow, lastCol);
                    rngDataDetails.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
                    rngDataDetails.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    //var excelTableDetails = rngTableDetails.CreateTable();
                    //ws.Columns().AdjustToContents(1, lastRow + 1);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
